package com.projectbox.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.projectbox.dao.SearchDao;
import com.projectbox.model.Project;
import com.projectbox.model.Resource;
import com.projectbox.model.User;

/**
 * Implementation of the SearchDao Interface
 * 
 * @author Pavel
 * 
 */
public class JdbcSearchDao implements SearchDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	private SimpleJdbcTemplate simpleJdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Utility method for setting the Calendar hour/min/seconds to zero
	 * 
	 * @param cal
	 */
	private void clearHrMinSec(Calendar cal) {
		cal.clear(Calendar.HOUR);
		cal.clear(Calendar.HOUR_OF_DAY);
		cal.clear(Calendar.MINUTE);
		cal.clear(Calendar.SECOND);
		cal.clear(Calendar.MILLISECOND);
	}

	/**
	 * Utility method to compare dates
	 * 
	 * @param startCalendar
	 *            the start date calendar object
	 * @param endCalendar
	 *            the end date calendar object
	 * @param calendar
	 *            the calendar with the project creation date
	 * @return true if the projectCalendar falls between the startCalendar and
	 *         endCalendar. False otherwise
	 */
	private boolean checkDates(Calendar startCalendar, Calendar endCalendar,
			Calendar calendar) {
		
		logger.debug("Start Calendar: " + startCalendar.getTimeInMillis());
		logger.debug("End Calendar: " + endCalendar.getTimeInMillis());
		logger.debug("Project Calendar: " + calendar.getTimeInMillis());
		return calendar.getTimeInMillis() >= startCalendar.getTimeInMillis() && calendar.getTimeInMillis() <= endCalendar.getTimeInMillis();

	/*if (startCalendar.before(calendar) && endCalendar.after(calendar)) {
			return true;
		} else if (startCalendar.getTimeInMillis() == calendar.getTimeInMillis() && endCalendar.after(calendar)){
			return true;
		} else if (startCalendar.before(calendar) && endCalendar.getTimeInMillis() == calendar.getTimeInMillis()){
			return true;
		}
			return false;*/
	}

	/**
	 * Searches project names
	 * 
	 * @param keyword
	 *            the search string
	 * @param startDate
	 *            the initial point of search
	 * @param endDate
	 *            the final point of search
	 */
	public List<Project> searchProjects(String keyword, Date startDate,
			Date endDate) {
		String sql = "select * from projects";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper());
		List<Project> searchResults = new ArrayList<Project>();
		Calendar startCalendar = null, endCalendar = null, projectCalendar = null;

		logger.debug("looking for projects");
		if (!startDate.equals(endDate)) {
			startCalendar = Calendar.getInstance();
			startCalendar.setTime(startDate);
			clearHrMinSec(startCalendar);

			endCalendar = Calendar.getInstance();
			endCalendar.setTime(endDate);
			clearHrMinSec(endCalendar);

			projectCalendar = Calendar.getInstance();
		}

		for (Project p : projects) {
			if (p.getName().toLowerCase().contains(keyword)) {
				if (!startDate.equals(endDate)) {
					projectCalendar.setTimeInMillis(p.getTimeCreated());
					clearHrMinSec(projectCalendar);
				}
				if (!startDate.equals(endDate)) {
					if (checkDates(startCalendar, endCalendar, projectCalendar)) {
						searchResults.add(p);
					}
				} else {
					logger.debug("start date equals end date");
					searchResults.add(p);
				}
			}
		}

		return searchResults;
	}

	/**
	 * Search Resources based on the input userId. if userId is negative, all
	 * the resources are searched. Otherwise only the resources relevant to the
	 * user is searched
	 * 
	 * @param userId
	 * @keyword the search keyword
	 * @param startDate
	 * @param endDate
	 * @return a list of resources
	 */

	public List<Resource> searchResources(long userId, String keyword,
			Date startDate, Date endDate) {

		String sql = "";
		if (userId < 0) {
			sql = "select * from resources r where r.pid NOT IN (select id from projects where privacy=1)";
		}

		else {
			sql = "select * from resources r where r.uid = " + userId
					+ " or r.pid IN (select id from projects where mid = "
					+ userId + ") "
					+ "or r.pid IN (select pid from accesses  where uid = "
					+ userId + " and status='granted')";
		}

		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper());
		List<Resource> searchResults = new ArrayList<Resource>();

		Calendar startCalendar = null, endCalendar = null, resourceCalendar = null;

		if (!startDate.equals(endDate)) {
			startCalendar = Calendar.getInstance();
			startCalendar.setTime(startDate);
			clearHrMinSec(startCalendar);

			endCalendar = Calendar.getInstance();
			endCalendar.setTime(endDate);
			clearHrMinSec(endCalendar);

			resourceCalendar = Calendar.getInstance();
		    
		}

		for (Resource r : resources) {
			if (r.getName().toLowerCase().contains(keyword)) {
				if (!startDate.equals(endDate)) {
					resourceCalendar.setTimeInMillis(r.getTimeCreated());
					clearHrMinSec(resourceCalendar);
					logger.debug("time created: "
							+ resourceCalendar.getTime().getTime()
							+ " startDate: "
							+ startCalendar.getTime().getTime() + "endDate: "
							+ endCalendar.getTime().getTime());
				}
				if (!startDate.equals(endDate)) {
				    
					if (checkDates(startCalendar, endCalendar, resourceCalendar)) {
						searchResults.add(r);
					}
				} else {
					searchResults.add(r);
				}
			}

		}

		return searchResults;
	}

	/**
	 * Returns a list of all the resource types
	 */
	public List<String> getResourceTypes() {
		String sql = "select distinct type from resources";
		List<String> typeList = simpleJdbcTemplate.query(sql,
				new RowMapper<String>() {
					public String mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return resultSet.getString(1);
					}
				});
		return typeList;
	}

	/**
	 * Checks the resource type
	 * 
	 * @param type
	 * @return true if the resource type exists in the database false otherwise
	 */
	public boolean doesResourceTypeExist(String type) {
		List<String> types = getResourceTypes();

		for (String t : types) {
			if (t.contains(type)) {
				return true;
			}
		}

		return true;
	}

	/**
	 * Returns a list of all resources
	 * 
	 * @return
	 */
	public List<Resource> getAllResources() {
		String sql = "select * from resources";
		return simpleJdbcTemplate.query(sql, new ResourceMapper());

	}

	public List<Project> getProjectsByManager(Long mid) {
		String sql = "select * from projects where mid = ?";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper(), mid);

		return projects;
	}

	/**
	 * Searches resources for a specific type of resources
	 * 
	 * @param type
	 *            the type of the resource
	 * @return a list of resources TODO: Checking the date attributes
	 */
	public List<Resource> searchResourcesWithType(String type, Date startDate,
			Date endDate) {
		if (!doesResourceTypeExist(type)) {
			return null;
		}
		String sql = "select * from resources r where r.pid NOT IN (select id from projects where privacy=1)";
		
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper());
		List<Resource> searchResults = new ArrayList<Resource>();

		Calendar startCalendar = null, endCalendar = null, resourceCalendar = null;

		if (!startDate.equals(endDate)) {
			startCalendar = Calendar.getInstance();
			startCalendar.setTime(startDate);
			clearHrMinSec(startCalendar);

			endCalendar = Calendar.getInstance();
			endCalendar.setTime(endDate);
			clearHrMinSec(endCalendar);

			resourceCalendar = Calendar.getInstance();
		    
		}

		for (Resource r : resources) {
			if (r.getType().toLowerCase().contains(type)) {
				if (!startDate.equals(endDate)) {
					resourceCalendar.setTimeInMillis(r.getTimeCreated());
					clearHrMinSec(resourceCalendar);
					logger.debug("time created: "
							+ resourceCalendar.getTime().getTime()
							+ " startDate: "
							+ startCalendar.getTime().getTime() + "endDate: "
							+ endCalendar.getTime().getTime());
				}
				if (!startDate.equals(endDate)) {
				    
					if (checkDates(startCalendar, endCalendar, resourceCalendar)) {
						searchResults.add(r);
					}
				} else {
					searchResults.add(r);
				}
			}

		}

		return searchResults;
		
	}

	/**
	 * RowMapper for Project objects.
	 */
	private static final class ProjectMapper implements RowMapper<Project> {
		public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
			Project project = new Project();
			project.setId(rs.getLong("id"));
			project.setName(rs.getString("name"));
			project.setDescription(rs.getString("description"));
			project.setPrivate(rs.getBoolean("privacy"));
			project.setMid(rs.getLong("mid"));
			project.setTimeCreated(rs.getLong("time_created"));
			project.setTimeModified(rs.getLong("time_modified"));
			return project;
		}
	}

	/**
	 * RowMapper for the Resource object
	 * 
	 */
	private static final class ResourceMapper implements RowMapper<Resource> {
		public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
			Resource resource = new Resource();
			resource.setId(rs.getLong("id"));
			resource.setName(rs.getString("name"));
			resource.setDescription(rs.getString("description"));
			resource.setType(rs.getString("type"));
			resource.setPath(rs.getString("path"));
			resource.setTimeCreated(rs.getLong("time_created"));
			resource.setTimeModified(rs.getLong("time_modified"));
			resource.setUid(rs.getLong("uid"));
			resource.setPid(rs.getLong("pid"));
			resource.setLastModId(rs.getLong("lastModId"));
			return resource;
		}
	}

	/**
	 * RowMapper for Users objects.
	 */
	private static final class UserMapper implements RowMapper<User> {
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User user = new User();
			user.setId(rs.getLong("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setEnabled(rs.getBoolean("enabled"));
			user.setFirstName(rs.getString("firstname"));
			user.setLastName(rs.getString("lastname"));
			return user;
		}
	}
}
